Introduction
Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.
For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:
These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.
The Adjacency List Model
Typically the example categories shown above will be stored in a table like the following (I’m including full CREATE and INSERT statements so you can follow along):
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL
);
INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)
In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see thatFLASH is a child ofmp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.
Retrieving a Full Tree
The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name = 'ELECTRONICS'; +-------------+----------------------+--------------+-------+ | lev1 | lev2 | lev3 | lev4 | +-------------+----------------------+--------------+-------+ | ELECTRONICS | TELEVISIONS | TUBE | NULL | | ELECTRONICS | TELEVISIONS | LCD | NULL | | ELECTRONICS | TELEVISIONS | PLASMA | NULL | | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL | | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL | +-------------+----------------------+--------------+-------+ 6 rows in set (0.00 sec)
Finding all the Leaf Nodes
We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query:
SELECT t1.name FROM category AS t1 LEFT JOIN category as t2 ON t1.category_id = t2.parent WHERE t2.category_id IS NULL; +--------------+ | name | +--------------+ | TUBE | | LCD | | PLASMA | | FLASH | | CD PLAYERS | | 2 WAY RADIOS | +--------------+
Retrieving a Single Path
The self-join also allows us to see the full path through our hierarchies:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH'; +-------------+----------------------+-------------+-------+ | lev1 | lev2 | lev3 | lev4 | +-------------+----------------------+-------------+-------+ | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | +-------------+----------------------+-------------+-------+ 1 row in set (0.01 sec)
The main limitation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity.
Limitations of the Adjacency List Model
Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent.
[amazon-related-products keywords=”joe celko trees”]
The Nested Set Model
What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way:
Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes:
CREATE TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
SELECT * FROM nested_category ORDER BY category_id;
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
We use lft and rgt because left and right are reserved words in MySQL, see http://dev.mysql.com/doc/mysql/en/reserved-words.html for the full list of reserved words.
So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right:
This design can be applied to a typical tree as well:
When working with a tree, we work from left to right, one layer at a time, descending to each node’s children before assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm.
Retrieving a Full Tree
We can retrieve the full tree through the use of a self-join that links parents with nodes on the basis that a node’s lft value will always appear between its parent’s lft and rgt values:
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+----------------------+
Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same parent as the lft values.
Finding all the Leaf Nodes
Finding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find the leaf nodes, we look for nodes where rgt = lft + 1:
SELECT name FROM nested_category WHERE rgt = lft + 1; +--------------+ | name | +--------------+ | TUBE | | LCD | | PLASMA | | FLASH | | CD PLAYERS | | 2 WAY RADIOS | +--------------+
Retrieving a Single Path
With the nested set model, we can retrieve a single path without having multiple self-joins:
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
+----------------------+
[amazon-related-products keywords=”mysql”]
Finding the Depth of the Nodes
We have already looked at how to show the entire tree, but what if we want to also show the depth of each node in the tree, to better identify how each node fits in the hierarchy? This can be done by adding a COUNT function and a GROUP BY clause to our existing query for showing the entire tree:
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| ELECTRONICS | 0 |
| TELEVISIONS | 1 |
| TUBE | 2 |
| LCD | 2 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 1 |
| MP3 PLAYERS | 2 |
| FLASH | 3 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 2 |
+----------------------+-------+
We can use the depth value to indent our category names with the CONCAT and REPEAT string functions:
SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
Of course, in a client-side application you will be more likely to use the depth value directly to display your hierarchy. Web developers could loop through the tree, adding <li></li> and <ul></ul> tags as the depth number increases and decreases.
Depth of a Sub-Tree
When we need depth information for a sub-tree, we cannot limit either the node or parent tables in our self-join because it will corrupt our results. Instead, we add a third self-join, along with a sub-query to determine the depth that will be the new starting point for our sub-tree:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
This function can be used with any node name, including the root node. The depth values are always relative to the named node.
Find the Immediate Subordinates of a Node
Imagine you are showing a category of electronics products on a retailer web site. When a user clicks on a category, you would want to show the products of that category, as well as list its immediate sub-categories, but not the entire tree of categories beneath it. For this, we need to show the node and its immediate sub-nodes, but no further down the tree. For example, when showing the PORTABLE ELECTRONICS category, we will want to show MP3 PLAYERS, CD PLAYERS, and 2 WAY RADIOS, but not FLASH.
This can be easily accomplished by adding a HAVING clause to our previous query:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
If you do not wish to show the parent node, change the HAVING depth <= 1 line to HAVING depth = 1.
Aggregate Functions in a Nested Set
Let’s add a table of products that we can use to demonstrate aggregate functions with:
CREATE TABLE product
(
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);
INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);
SELECT * FROM product;
+------------+-------------------+-------------+
| product_id | name | category_id |
+------------+-------------------+-------------+
| 1 | 20" TV | 3 |
| 2 | 36" TV | 3 |
| 3 | Super-LCD 42" | 4 |
| 4 | Ultra-Plasma 62" | 5 |
| 5 | Value Plasma 38" | 5 |
| 6 | Power-MP3 128mb | 7 |
| 7 | Super-Shuffle 1gb | 8 |
| 8 | Porta CD | 9 |
| 9 | CD To go! | 9 |
| 10 | Family Talk 360 | 10 |
+------------+-------------------+-------------+
Now let’s produce a query that can retrieve our category tree, along with a product count for each category:
SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
nested_category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
+----------------------+---------------------+
| name | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS | 10 |
| TELEVISIONS | 5 |
| TUBE | 2 |
| LCD | 1 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 5 |
| MP3 PLAYERS | 2 |
| FLASH | 1 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 1 |
+----------------------+---------------------+
This is our typical whole tree query with a COUNT and GROUP BY added, along with a reference to the product table and a join between the node and product table in the WHERE clause. As you can see, there is a count for each category and the count of subcategories is reflected in the parent categories.
Adding New Nodes
Now that we have learned how to query our tree, we should take a look at how to update our tree by adding a new node. Let’s look at our nested set diagram again:
If we wanted to add a new node between the TELEVISIONS and PORTABLE ELECTRONICS nodes, the new node would have lft and rgt values of 10 and 11, and all nodes to its right would have their lft and rgt values increased by two. We would then add the new node with the appropriate lft and rgt values. While this can be done with a stored procedure in MySQL 5, I will assume for the moment that most readers are using 4.1, as it is the latest stable version, and I will isolate my queries with a LOCK TABLES statement instead:
LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
We can then check our nesting with our indented tree query:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let’s add a new FRS node below the 2 WAY RADIOS node:
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft FROM nested_category
WHERE name = '2 WAY RADIOS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;
In this example we expand everything to the right of the left-hand number of our proud new parent node, then place the node to the right of the left-hand value. As you can see, our new node is now properly nested:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
Deleting Nodes
The last basic task involved in working with nested sets is the removal of nodes. The course of action you take when deleting a node depends on the node’s position in the hierarchy; deleting leaf nodes is easier than deleting nodes with children because we have to handle the orphaned nodes.
When deleting a leaf node, the process if just the opposite of adding a new node, we delete the node and its width from every node to its right:
LOCK TABLE nested_category WRITE; SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM nested_category WHERE name = 'GAME CONSOLES'; DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight; UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight; UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight; UNLOCK TABLES;
And once again, we execute our indented tree query to confirm that our node has been deleted without corrupting the hierarchy:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
This approach works equally well to delete a node and all its children:
LOCK TABLE nested_category WRITE; SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM nested_category WHERE name = 'MP3 PLAYERS'; DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight; UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight; UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight; UNLOCK TABLES;
And once again, we query to see that we have successfully deleted an entire sub-tree:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent:
LOCK TABLE nested_category WRITE; SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM nested_category WHERE name = 'PORTABLE ELECTRONICS'; DELETE FROM nested_category WHERE lft = @myLeft; UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight; UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight; UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight; UNLOCK TABLES;
In this case we subtract two from all elements to the right of the node (since without children it would have a width of two), and one from the nodes that are its children (to close the gap created by the loss of the parent’s left value). Once again, we can confirm our elements have been promoted:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+---------------+
| name |
+---------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+---------------+
Other scenarios when deleting nodes would include promoting one of the children to the parent position and moving the child nodes under a sibling of the parent node, but for the sake of space these scenarios will not be covered in this article.
Final Thoughts
While I hope the information within this article will be of use to you, the concept of nested sets in SQL has been around for over a decade, and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko’s Trees and Hierarchies in SQL for Smarties, written by a very respected author in the field of advanced SQL, Joe Celko. Joe Celko is often credited with the nested sets model and is by far the most prolific author on the subject. I have found Celko’s book to be an invaluable resource in my own studies and highly recommend it. The book covers advanced topics which I have not covered in this article, and provides additional methods for managing hierarchical data in addition to the Adjacency List and Nested Set models.
In the References / Resources section that follows I have listed some web resources that may be of use in your research of managing hierarchal data, including a pair of PHP related resources that include pre-built PHP libraries for handling nested sets in MySQL. Those of you who currently use the adjacency list model and would like to experiment with the nested set model will find sample code for converting between the two in the Storing Hierarchical Data in a Database resource listed below.
[amazon-related-products keywords=”database”]
References / Resources
- Joe Celko’s Trees and Hierarchies in SQL for Smarties – ISBN 1-55860-920-2
- Storing Hierarchical Data in a Database
- A Look at SQL Trees
- SQL Lessons
- Nontraditional Databases
- Trees in SQL
- Trees in SQL (2)
- Converting an adjacency list model to a nested set model
- Nested Sets in PHP Demonstration (German)
- A Nested Set Library in PHP
Comments
136 responses to “Managing Hierarchical Data in MySQL”
Very nice and concise article, Mike. I bought Joe Celko’s book that you referenced and have skimmed, then read it. Your article helps me to understand the Nested Set model. I’m an old dog who has been writing adjacency list hierarchies since the early nineties. I always thought there could be ‘better way’, and this is certainly it. Thanks for taking the time to explain it all, Mike.
Could this be made to work for hierarchies where a child can have more than one parent?
At that point you’re dealing with a graph instead of a hierarchy. If you’re using MySQL you may want to look at the following storage engine by an associate of mine: http://openquery.com/products/graph-engine
Hi Mike;
thanks for your well described tutorial. I noticed you do a “GROUP BY node.name” in quite a few example queries here. This is very bad practice and will return some unexpected results as soon as you have a few hundred records where node.name is not unique anymore.
You should group your rows by ID instead.
I am a beginner php programmer and want to develop a mlm web application in php & mysql,but I have no idea about it.
My mlm webapplication’s requirement is-
1.It work on binary tree in which every node only have left and right child.
2.In this we can add node as our wish which be add in left or right.
So how can implement it?
Very useful link to see example of self joins
Here you can select user and parent from same table
http://www.devshed.com/c/a/MySQL/MySQL-Table-Joins/4/
what i use is here:
select distinct e.employeeid,e.givenname,e.contact_no,b.givenname as parent_name,e.status from employee as e ,employee as b, agent_details as a,user_group as u
where a.agent_id = e.employeeid AND a.parent_id=b.employeeid AND e.employeeid=u.employeeid AND u.groupid=10 GROUP BY e.employeeid ORDER BY e.givenname
I use this data model for years. I first found it on dev.mysql.com few years back. I have structures many levels deep and data with hundreds of rows – It never fails, it’s really fast. The ability to cut part of the structure with one question is simply awesome! It is part of my framework now with full credit to Mike of course 🙂 Thank you so much for sharing.
How do you manage inserts? I beleive you would have a programmatic way of doing inserts. Most online resources on this subject start from “pen-paper”, as in, number nodes/elements by hand and then fire individual inserts. Is there a resource that takes in a tree/xml and generates the numbering?
I like your code for “Retrieving a Single Path” in a “Nested Set Model”.
However, how would you retrieve a single path if there were multiple nodes with the same name? For example, what if Flash was listed both under MP3 Players and under Movie Characters, and you wanted to retrieve the path that led to the movie character only?
Your adjacency model is super close to what I need. Is it possible to perform the “retrieve a full tree” query and have the number of “children” be dynamic. Your example will work great if you have =1 levels.
Awesome tutorial. I am really impressed. Its something that i didn’t know and i’ll gladly use it 🙂 Thank your for taking you time to write it.
Very nice explanation of the adjacent list and nested set models,
thanks a lot !
really very help full for developing my project
super like
Thanks
In your demonstration of inserting a row between ‘Televisions’ and ‘Portable Electronics’, what if the table was extremely large, perhaps hundreds of thousands of rows, and the left insert point were very close to zero? Unlike an adjency list, such an insert into a nested set will trigger massive updates.
It seems to me that a good extension to the model might be to reserve space, perhaps depth dependent, so insertions trigger less expansive updates.
Very useful article – I had to do some digging, as this article recently vanished from mysql.com for some reason. The formatting of this article on your blog is somewhat broken – it would be nice if you could clean this up so it’s legible again. Thanks!
[…] have thought about using a single hierarchical table like that on: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ that i know can output a list like […]
Firstly, huge thanks for a great & clear explanation.
I implemented some of your code examples against a database I’d generated of a hard disk directory structure and got some strange results.
In the examples you’ve given, you’re using ‘GROUP BY node.name’. However, if I understand things correctly, this will only work if ‘node.name’ is unique for all entries.
If, as I discovered, you have multiple entries with the same ‘node.name’ then doing ‘GROUP BY node.category_id’ seems to work.
[…] Take a look at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for detail information on hierarchical relation in SQL. Assuming you have read this link, the […]
How big of strain does this place on the Db when the amount of categories gets large? For example, say you have 3 main categories, Electrnics, Clothing, and groceries. Say each of those has 200 total nodes underneath each. When adding a new node to the very beginning seems like it could place a big strain in terms of having to update the lft and rgt of hundreds of nodes. At what point does size hinder performance?
[…] this made you curious, I recommend reading this article http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/. The examples in the article are based on Joe Celko’s ‘Trees and Hierarchies in SQL for […]
+1 to you Mike, I had the same table schema but implemented the retrieval stuff in PHP code which was a bit slow (I used it for categories on a retail store portal). I was looking for an idea about hierarchical retrieval via SQL queries, and you gave me a great food for thought.
Hi,Nice article BUT ON Adjacency List Model Getting CAt->SUbs YOU HAVE WRONG QUERY OR NOT PROPER QUERY, I CANT LIST
LIKE THIS :
Missing cats-subs
a-> (I need this id)
a->b (Also this id)
Your Cat-subs
a->b->c (NO directly THIS ID)
a->b->c->d
…
YOUR QUERY PROBLEM IS :
CORRECT QUERY MUST BE
———————–
ELECTRONICS | NULL |NULL | NULL |
ELECTRONICS | TELEVISIONS |NULL | NULL |
ELECTRONICS | TELEVISIONS |TUBE | NULL |
YOURQUERY
————————————
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
AND the Question IS HOR TO GET LIKE THIS ?
Great article. Really helpful. Quick question though. Let’s say I want to get the entire tree but the depth be an offset from the current queried node. For example (querying for Portable Electronics):
Electronics = -1
Portable Electronics = 0
MP# Players = 1
CD Player = 1
2 Way Radio = 1
Flash = 2
Great article, I looked for it on dev.mysql but it’s not ther anymore.
One question on Adjacency List Model:
Let’s say you have in table also
positionINT field, that let’s you sort nodes that are on the same level, in your example let’s say I want “LCD” to come before “TUBE”, or I want “PORTABLE ELECTRONICS” to come before “TELEVISION”.Is it still possible to retrieve the full tree with one single query as in your example, but with the nodes already ordered also by position?
Cause I think adding just
ORDER BY positionat the end of the query would not be a solution.Great article Mike! I have a menu structured in the adjacency list model and I needed to retrieve a single path where the last node could be at any level. I ended up writing a stored procedure that would return one string the different nodes separated by semi-colon and have PHP parse that string.
The code is something like this, bear in mind I’m not the best coder and I’m sure it can be optimized:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetMenuPath`(IN `myNode` VARCHAR(50), OUT `menuPath` VARCHAR(200)) NO SQL begin declare myParent int; declare myNewParent int; declare myNewPath varchar(200); set menuPath = myNode; select menu_parent into myParent from menu where menu_item = myNode; while myParent > 0 do /* Not zero or null */ select concat(menuPath, ';', menu_item) into myNewPath from menu where menu_id = myParent; set menuPath = myNewPath; select menu_parent into myNewParent from menu where menu_id = myParent; set myParent = myNewParent; end while; endIf I may point one possible improvement over your model. When you do your SELECT and GROUP BY statements, to use the ID instead of the name. Imagine you have child nodes with the same name, but different parents.
Good article! In addition, use this query to simulate the “old-way” adjacency list model table structure:
SELECT child.category_id AS category_id, child.name AS name, parent.category_id AS parent FROM nested_category AS child LEFT JOIN nested_category AS parent ON parent.lft = ( SELECT MAX(anchestor.lft) FROM nested_category AS node, nested_category AS anchestor WHERE anchestor.lft node.rgt AND node.category_id = child.category_id ) ORDER BY child.lft;1) It’s okay to have credits hole in the series?
| name | lft | rgt |
| ELECTRONICS | 1 | 8 |
| TELEVISION | 2 | 3 |
| PORTABLE ELECTRONICS | 6 | 7 |
2) is this a good idea?
| Name | lft | rgt | parent_lft | parentrgt |
| ELECTRONICS | 1 | 8 | NULL | NULL |
| TELEVISION | 2 | 3 | 1 | 8 |
| PORTABLE ELECTRONICS | 6 | 7 | 1 | 8 |
3) what is the easiest way to convert a tree to a Nested Set?
I have a category structure up to 5 level
Music -> Pop -> Michael Jackson -> Thriller -> Thriller – Song
Suppose i want to search the category with like query ‘%s%’.
Now s is in 3 level (Level1 = Music),(Level3 = Michael Jackson ),(Level5 = Thriller – Song ).
Now its give me 3 results instead of 1. How i will get 1 results?
If i found the result at first level(levle1), it will not search at second level (level2).
if i found the result at Second level(levle2), it will not search at Third level (level3) and so on.
Have you idea?
Thank you in advance for this article. Could you show how to add new root nodes ?
Heck yeah this is excltay what I needed.
In the ordering for the nested set, I believe it should be
‘ORDER BY parent.lft’ as opposed to ‘node.lft’.
If we look at the Retrieving a Single Path section, it does not make sense to order by node.lft, since node.lft is all the same tuple (we’ve queried for a node.lft=’FLASH’).
If we had a “SELECT parent.name, node.name …” it would like like the following:
name | name
————————————————
MP3 PLAYERS | FLASH
FLASH | FLASH
ELECTRONICS | FLASH
PORTABLE ELECTRONICS | FLASH
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
Hello
I have set up my page with nested tree. But the biggest problem is still when I add a new category/subcategory. Afterwards you have to renumber all articles in your database because most of them got a new category number. If you have 500’000 acticle this takes long and all your references change too.
I wonder how ebay is doing this? You can’t stop trading while updating.
Hello Heinz, look at Trees and Hierarchies in SQL for Smarties to get a lot of advanced tips. One I can share here is to look at using index numbers that increment by 1,000 instead of by one, when you need to add a node you place it between two existing index numbers, then occasionally during slow periods you re-index everything.
very nice article. But now i have a question. how can i receive directly a specified depth of a optional parent? For example, i choose PORTABLE ELECTRONICS as the parent and i want to get the depth:2 which will be resulted the FLASH.
Can u help me?
First compliments on article – it’s really straight & forward explaining approach concepts of building hierarchical model in RDBS.
But, with all respect you can not take full advantage of using it in real world in MySQL DB engine without applied knowledge in structuring and using Spatial Data Type extension… cause using default (BTREE) index to perform nested set join like
FROM nested_table AS node, nested_category AS parent
will chock a query on performance in mega volume sets.
So in MySQL, LFT RGT columns define as geometrical data type is highly recommended and so self cartesian product join will look something like:
FROM nested_table AS node
JOIN nested_table AS AS parent ON (MBRWithin(Point(0, node.lft), parent.lft_rgt_range))
or close to it 🙂
Cheers
Misha is correct.
We’ve built an Enterprise marketplace product and converted from Adj. to Nested. It’s incredible how fast result sets are accomplished with the proper query. While the queries in this article work they are definitely not large-category + huge product volume set ready.. everything works great with < 1,000 products and/or < 10,000 categories.
What's missing from this article IMO is the spatial aspect on the table joins for the categories. For example:
products table contains 1 million products
categories table contains 28,301 categories.
How we accomplish the finishing touches to make it production ready was the following:
SELECT …
FROM category AS node
JOIN category AS parent ON (MBRWithin(Point(0, node.lft), parent.sets))
…..
Without it a category with 11,000 items results in 18 seconds delay. With the ON clause to the parent table join reduced it to < 0.1 second. It's blazing fast. We've also added necessary single and combined indexes which also help I'm sure.
You'll need (if your category table is named category):
ALTER TABLE category ADD `sets` LINESTRING NOT NULL
UPDATE category SET `sets` = LineString(Point(-1, lft), Point(1, rgt))
CREATE SPATIAL INDEX sx_categories_sets on category (sets)
If you do your queries a different way, we do it 2 different ways; you can try this one as well:
SELECT …
FROM category AS parent
JOIN category AS midpoint
JOIN category AS node
….
AND (MBRWithin(Point(0, node.lft), parent.sets))
Let me know if it helps!
Cheers!
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] You can also take a look here […]
[…] You should consider using Nested Sets to store your tree structure. Additionally you can improve the whole thing by using recursive […]
[…] I am using Nested Tree Model […]
Hello friends. I’ve just switched from Adjacency List Model to nested set model.
I’think i’m starting to understand what is going on, but i have big problem – i cannot draw my tree as html menu. i found some sources/examples (http://stackoverflow.com/questions/1310649/getting-a-modified-preorder-tree-traversal-model-nested-set-into-a-ul) on the net, but it’s a little hard to me to port them to work with Your examples. Can anybody help me with that? Thank you.
Hey there,
Just wanted to thank you for the article. I was working out ways to best get results from a nested set and my mind was turning to mush. Luckily I had a read through this article and all became clear.
Cheers
[…] am using the following article from Mike Hillyer as a basis for creating a nested set model for use with comments and comment […]
[…] using the nested set model for my database (from here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), it works perfectly, […]
[…] this is at the database design stage. Your categories table needs to be a Nested Set. The article Managing Hierarchical Data in MySQL is not that MySQL specific (despite the title), and gives a great overview of the different methods […]
[…] Representing hierarchies in MySQL: very good overview of Nested Set in particular […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
Hi, I’ve implemented nsm in my project, thanks to this article.
Now I’m trying to get a simple method of showing all (lets say) products) of given parent category (which can contain many subcategories). Is there any chance to somebody give any tip on how to acheive this?
best regards
Adam
Under Retrieving a Single Path, you can/should leave out (excuse the pun) the
“t1.name = ‘ELECTRONICS’ AND”
part of the WHERE clause. The query will find the path to the root.
[…] Manipulations-Methoden interessiert sind, empfiehlt Marc Alff Mike Hillyers Artikel “Managing Hierarchical Data in MySQL“. Die aktuellle Dokumentation des Performance-Schemas findet sich auf […]
I tried Retriving a single path and your query didn’t worked. I changed ‘ORDER BY node.lft’ to ‘ORDER BY parent.lft’ and worked. My mysql is 5.6.19-1~exp1ubuntu2. Excellent work! thanks a lot.
[…] using the “nested set model” (the backend used by our web application is CakePHP and the Project model uses the […]
Finding the Depth of the Nodes:
SELECT node.name, (COUNT(parent.name) – 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
I find this is problematic, cause if I add two item with the same name will cause the depth got a wrong result..plz help me..
Thanks !!!
[…] found http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/, this is pretty much what I need except my model is […]
[…] there two ways work hierarchy info in mysql: […]
[…] on http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] studied Adjacency List, Nested List & Closure table design, but came to conclusion that Session based Adjacency List can be better […]
Awesome idea! I knew this method existed and after reading this I also know how to implement it. Thank you very much 🙂
thanks a lot !
[…] Representing hierarchies in MySQL: very good overview of Nested Set in particular […]
Finding the Depth of the Nodes,
select
node.id as id,
node.lft as lft,node.rgt as rgt,
parent.id as top_parent_id,
(count(parent.id)-1) as depth
FROM
nested_category as parent_ctl,
nested_category as parent,
nested_category as node
where
parent.lft between parent_ctl.lft and parent_ctl.rgt
and node.lft between parent.lft and parent.rgt
and parent_ctl.id = ?
group by node.id
order by node.lft
* title -> id
[…] This website lists some common techniques: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
Very usefull, thanks.
Man, you save our lives. Thanks so much! Nice job and was the best tutorial about this issue. Best regards
Insert a new node
——————————-
LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = ‘TELEVISIONS’;
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES(‘GAME CONSOLES’, @myRight + 1, @myRight + 2);
UNLOCK TABLES;
————————-
This gives Game Console left=10 and right = 11, while right of Television remains 9. Resulting a corrupted tree.
Instead, you can select @myRight := rgt – 1, and rest of the query would work fine.
Regards
In section RETRIEVING A SINGLE PATH, order by parent.lft not node.lft
Fantastic article Mike!
There is a further improvement to this model where you store the depth with each node. That makes querying for sub-trees and adjacent entities far simpler. In symphony there is a nested set library available for doctrine (which I have used) and there’s also a php lib called Boabab (which I haven’t).
Great article.
How to move a node?
Thanks for this awesome article. It shows the deep of concepts which author obtained. Bravo
There’s a question i’m thinking about it:
How we can build a lft,rgt type (The Nested Set) tree with an Adjacency List tree which already we have?
imagine there’s a table : id,parent_id,title with 500000 or more records.
How can convert this table?
Regards.
I found this great soloution:
http://dba.stackexchange.com/questions/89051/stored-procedure-to-update-an-adjacency-model-to-nested-sets-model
First thank you for the post, it’s really helpful. I have a question, maybe a silly one, but in case I have two parents, for instance, following your reasoning, suppose we have Electronics and Home appliance as the topmost parents, so this model would work the same? Just adding another topmost category as NULL? Tks in advance.
Yes, you will have an un-displayed parent, and the first two children are Electronics and Home.
[…] werden. Stattdessen lässt sich zum Beispiel das „Nested Set Model“ verwenden. Dieser Beitrag beschreibt sehr schön das „Nested Set Modell“ (englisch). Bei fokus² haben wir noch die rekursive Variante implementiert, da ich aufgrund der Erfahrung […]
I want to make a single query that works multiple child level
and i want to this with single sql query
Thankyou.
[…] Great article … […]
Thank you so..much for this great blog. Awesome explanation. You are clear my concept of parent , child structure in 2 hours.
[…] is a detailed explanation. Unfortunately the article on mysql.com is does not exist any […]
Is it possible to use the nested set model with intersecting sets? For example, if there is a Portable Television, so it should be a shared child between Television and Portable Electronics. Can the nested set model be applied to such a case?
Keep the data on the television in a separate table, and put the primary key of the television in the nested set under television and under portable electronics.
[…] Representing hierarchies in MySQL: very good overview of Nested Set in particular […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
I think it would be easy to make a hierarchical table if the DBMS implement a numeric data type as a software version, eg.: 1.3.7.25, so we could use this type to do a column “Hierarchy”, where the first node would be the 1, her first child would be 1.1, his second child 1.2 , the first child of the first child would be 1.1.1, the second child of the first child would be 1.1.2 and so on. To insert correctly in the hierarchy just think in trigger to do it. To delete we could use the “Hierarchy” column to remove a parent and all its children.
PS.: It is just a initial idea.
In addition; To get the resultset hierarchically just order by “hierarchy”.
Clean, easy and graceful.
very good, realy helpful
thank you
Really useful article, thank you so much!
You can get calculated columns parent & depth with following query:
SELECT node.category_id, node.name,
node.lft, node.rgt,
(select parent.category_id from nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
ORDER BY parent.rgt offset 1 limit 1) AS parent,
(select (COUNT(parent.name) – 1) from nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt) as depth
FROM nested_category AS node;
This is great article. I’m going develop an ORM that will handle nested sets and hide the complexities. Unfortunately most ORMs do no support nested sets and typing all the necessary SQL for managing nested sets in every project that needs this feature is a nightmare.
[…] are probably definitely several articles out there which cover the SQL implementation of the Nested Set Model, aka “modified […]
Thank you. Great article.
I think this is more easy to read. 🙂
SET @SUB_TREE_NAME = ‘TELEVISIONS’;
SET @LFT = (SELECT lft
FROM nested_category
WHERE name = @SUB_TREE_NAME);
SET @RGT = (SELECT RGT
FROM nested_category
WHERE name = @SUB_TREE_NAME);
SET @SUB_TREE_DEPTH = (
SELECT COUNT(node.category_id) – 1
FROM
nested_category AS node
INNER JOIN
nested_category AS parent
ON
node.lft BETWEEN parent.lft AND parent.rgt
WHERE node.name = @SUB_TREE_NAME
GROUP BY node.category_id
);
SELECT
*,
(COUNT(node.category_id) – @SUB_TREE_DEPTH – 1) AS depth
FROM nested_category AS node LEFT JOIN nested_category AS parent ON node.lft BETWEEN parent.lft AND parent.rgt
WHERE node.lft BETWEEN @LFT AND @RGT
GROUP BY node.category_id
ORDER BY node.lft;
[…] are many ways to store a tree in a relational database, this is not by far the best option to do it, however it is still common to see it […]
[…] 原文地址 ，原文中 Hierarchical Data 直译为 分层结构 ，这里我翻译成 树状结构 。 […]
[…] of them, but if you’re interested in a more detailed explanation of the different models, I found this post very helpful for understanding the adjacency list and nested set models and the following articles […]
[…] From the blog Managing Hierarchical Data in MySQL […]
[…] can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set […]
[…] is a detailed explanation. Unfortunately the article on mysql.com is does not exist any […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
Very useful, as I heard first time about “lft” & “rgt”.
Very good detail articles especially diagram that cleared my doubt
[…] Representing hierarchies in MySQL: very good overview of Nested Set in particular […]
[…] Managing Hierarchical Data in MySQL […]
[…] One is the classic but rather mindbending nested sets. […]
[…] can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set […]
[…] This link should help you out: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] Managing Hierarchical Data in MySQL […]
[…] I am using a nested-set tree structure in a table. The concept is described here. […]
[…] this article: Managing Hierarchical Data in MySQL. It will give you a good starting point on how to implement the various […]
[…] This website lists some common techniques: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] Managing Hierarchical Data in MySQL […]
[…] can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set […]
[…] Mike Hillyer: Managing Hierarchical Data in MySQL […]
[…] Managing Hierarchical Data in MySQL […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] Managing Hierarchical Data in MySQL – not as clear as the above. […]
[…] Managing Hierarchical Data in MySQL […]
[…] representing trees in SQL. 4 are discussed by Bill Karwin here (and in his book which I recommend). This paper is also interesting (MySQL specific). See here (answer by Eldrith Conundrum) for a comparison […]
[…] Managing Hierarchical Data in MySQL […]
[…] to clarify, I need to recalculate the numeric lft/rght values of a nested set, not the ids of neighboring […]
[…] Managing Hierarchical Data in MySQL – not as clear as the above. […]
[…] Representing hierarchies in MySQL: very good overview of Nested Set in particular […]
[…] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ […]
[…] (which you are using) and nested sets. There is a very good write-up about these alternatives in Managing Hierarchical Data in MySQL. You can only do what you want in a single query with the nested set model. However, the nested set […]
[…] have a nested-set model in my database and I would like to ensure than when a parent node is somehow deleted from it, all […]
[…] is the “put a FK to your parent” method, i.e. each records points to it’s […]
[…] you were willing to change the tree representation to use the Nested Set Model, then the query might look […]
[…] should really consider using the Modified Preorder Tree Traversal which makes such queries much easier. Here’s your table expressed with MPTT. I have left the […]
[…] to this problem is to implement pre-ordered tree traversal on your set of hierarchical data. See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for an example […]
[…] puede usar el «Modelo de conjunto anidado» de Celko para recuperar todos los padres. Esto requerirá modificar la estructura de la tabla, de manera que […]
[…] was considering using the example put forth by the article Managing Hierarchical Data in MySQL, however it is adding the above list as records inside the table….and I’m not sure if […]
[…] I have an “event” table. For simplicity, you can imagine that it should be like a hierarchical category. It uses the nested set model (Thanks to Mark Hillyer for his post at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) […]
[…] been looking everywhere, and reading this http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/, but i didn’t find the result i was looking for..any help would be appreciated, […]
[…] There are two basic methods for doing this: adjacency lists and nested lists. Take a look at Managing Hierarchical Data in MySQL. […]
[…] with in MySQL (or most any other relational database). You might want to check out this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ on managing hierarchical data in MySQL. You might for example find better read performance from […]